﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Diagnostics;

namespace AutoPageHelper
{
    public class ExcelSource: IDataSource
    {
        DataSet dsData = new DataSet();
        string strColResult = "结果";
        string strConn = "";
        Dictionary<string, int> cursors = new Dictionary<string, int>();
        string defaultTableName;
        public ExcelSource()
        {
            defaultTableName = "";
        }
        public ExcelSource(string excelFile)
            : this()
        {
            if (Load(excelFile))
            {
                for (int i = 0; i < dsData.Tables.Count; i++)
                {
                    cursors[dsData.Tables[i].TableName] = 0;
                }
            }
            
        }
        public ExcelSource(string excelFile, string colNames)
            :this()
        {
            if (Load(excelFile, colNames))
            {
                for (int i = 0; i < dsData.Tables.Count; i++)
                {
                    cursors[dsData.Tables[i].TableName] = 0;
                }
            }
        }
        public int RowIndex
        {
            get { return cursors[DefaultTableName]; }
            set { cursors[DefaultTableName] = value; }
        }

        public string DefaultTableName
        {
            get 
            {
                if (string.IsNullOrEmpty(defaultTableName) && dsData.Tables.Count > 0)
                    defaultTableName = dsData.Tables[0].TableName;
                return defaultTableName; 
            }
            set 
            {
                if (dsData.Tables.Count > 0)
                    defaultTableName = dsData.Tables[value].TableName;
                else
                    defaultTableName = value;
            }
        }

        public string[] TableNames
        {
            get
            {
                return cursors.Keys.ToArray<string>();
            }
        }

        public DataTable DefaultTable
        {
            get { return dsData.Tables[DefaultTableName]; }
        }

        public string ResultColumn
        {
            get { return strColResult; }
            set
            {
                if (DefaultTable.Columns.Contains(value))
                    strColResult = value;
            }
        }

        public DataRow CurrentRow
        {
            get { return GetCurrentRow(); }
        }
        public DataRow GetCurrentRow()
        {
            return GetCurrentRow(DefaultTableName);
        }
        public DataRow GetCurrentRow(string table)
        {
            int i = cursors[table];
            if(i < dsData.Tables[table].Rows.Count && i >= 0)
                return dsData.Tables[table].Rows[i];
            return null;
        }
        public object GetData()
        {
            if (RowIndex < 0 || RowIndex >= DefaultTable.Rows.Count)
                return null;
            Dictionary<string, string> data = new Dictionary<string, string>();
            foreach (DataColumn col in DefaultTable.Columns)
            {
                data.Add(col.ColumnName, CurrentRow[col].ToString());
            }
            return data;
        }
        public string this[string column]
        {
            get
            {
                if (RowIndex < 0 || RowIndex >= DefaultTable.Rows.Count
                    || !DefaultTable.Columns.Contains(column))
                    return null;
                return CurrentRow[column].ToString();
            }
            set
            {
                if (RowIndex < 0 || RowIndex >= DefaultTable.Rows.Count)
                    return;
                if (DefaultTable.Columns.Contains(column))
                    CurrentRow[column] = value;
            }
        }
        public string this[int columnIndex]
        {
            get
            {
                if (RowIndex < 0 || RowIndex >= DefaultTable.Rows.Count)
                    return null;
                return CurrentRow[columnIndex].ToString();
            }
            set
            {
                if (RowIndex < 0 || RowIndex >= DefaultTable.Rows.Count
                    || columnIndex >= DefaultTable.Columns.Count || RowIndex < 0)
                    return;
                CurrentRow[columnIndex] = value;
            }
        }
        public string Result
        {
            set { this[strColResult] = value; }
        }
        public void MoveFirst()
        {
            cursors[DefaultTableName] = 0;
        }
        public void MoveLast()
        {
             RowIndex = DefaultTable.Rows.Count - 1;
        }
        public bool MoveNext()
        {
            return MoveNext(DefaultTableName);
        }

        public bool MoveNext(string table)
        {
            cursors[table]++;
            if (cursors[table] >= dsData.Tables[table].Rows.Count)
                return false;
            else
                return true;
        }

        public bool MovePrevious()
        {
            return MovePrevious(DefaultTableName);
        }
        public bool MovePrevious(string table)
        {
            cursors[table]--;
            if (cursors[table] < 0)
                return false;
            else
                return true;
        }
        
        public bool Load(string excelFile, string strColumns = "*")
        {
            if (excelFile.Substring(excelFile.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;ReadOnly=False;HDR=Yes;IMEX=0\"";

            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                dsData.Clear();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    string sheet = schemaRow["TABLE_NAME"].ToString();
                    try
                    {
                        if (!sheet.EndsWith("_"))
                        {
                            OleDbCommand cmd = new OleDbCommand("SELECT " + strColumns + " FROM [" + sheet + "]", conn);
                            cmd.CommandType = CommandType.Text;
                            OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
                            adp.Fill(dsData, sheet);
                            if (dsData.Tables[sheet].Columns.Count < 1 || dsData.Tables[sheet].Rows.Count < 1)
                            {
                                dsData.Tables.Remove(sheet);
                            }
                        }
                    }
                    catch (System.Exception ex)
                    {
                        Debug.WriteLine("Load data error: " + ex.Message + " @ "+ excelFile + ": " + sheet );
                    }
                    
                }
            }
            return (dsData.Tables.Count > 0);
        }

        public void Save()
        {
            Save(DefaultTableName);
        }

        public void Save(string table)
        {
            DataTable dt = dsData.Tables[table];
            if (!dt.Columns.Contains(strColResult))
                return;
            String strUpdate = "UPDATE [" + table + "] SET [" + strColResult + "] = '{0}' WHERE ";
            int index = 1;
            
            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.ColumnName != strColResult)
                    strUpdate += "[" + dc.ColumnName + "] = {" + index++ + "} AND ";
            }
            strUpdate = strUpdate.Substring(0, strUpdate.Length - 5);
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                foreach (DataRow dr in dt.Rows)
                {
                    String strSql = strUpdate;
                    index = 1;
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName != strColResult)
                        {
                            switch (dc.DataType.Name)
                            {
                                case "String":
                                case "DateTime":
                                    strSql = strSql.Replace("{" + index++ + "}", "'" + dr[dc.ColumnName].ToString() + "'");
                                    break;
                                case "Double":
                                    strSql = strSql.Replace("{" + index++ + "}", dr[dc.ColumnName].ToString());
                                    break;
                                default:
                                    strSql = strSql.Replace("{" + index++ + "}", dr[dc.ColumnName].ToString());
                                    break;
                            }

                        }
                        else
                        {
                            strSql = strSql.Replace("{0}", dr[dc.ColumnName].ToString());
                        }
                    }

                    try
                    {
                        OleDbCommand cmd = new OleDbCommand(strSql, conn);
                        cmd.ExecuteNonQuery();
                    }
                    catch (System.Exception)
                    {
                    }
                } // foreach col
            } // using conn
        } // save
    } // class
}
